﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.IO;
using NPOI.HSSF.Util;
using System.Reflection;
using System.Linq.Expressions;
public partial class ExcelHelper : System.Web.UI.Page
{
    private SqlConnection con = null;
    private string sqlStr = string.Empty;
    private SqlHelper sqlHelper = null;
    private SqlCommand cmd = null;
    private SqlDataReader reader = null;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["userName"] != null)
        {
            if (!Page.IsPostBack)
            {
                string type = Request.QueryString["type"];
                switch (type)
                {
                    case "master":
                        Output_Master_Excel();
                        break;
                    case "detail":
                        Output_Details_Excel();
                        break;
                }
            }
        }
        else
        {
            Response.Redirect("logon.htm");
        }
    }
    /// <summary>
    /// 获取销售主档的列表信息
    /// </summary>
    /// <returns></returns>
    private IList<SaleInfo> Get_Master_List(string sdate, string edate, string clientId)
    {
        if (Cache["master"] != null)
        {
            return (IList<SaleInfo>)Cache["master"];
        }
        else
        {

            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("select s.ClientId,c.ClientName,left(MakeBillDate,7) as month,SUM(Money) as sale,isnull(b.BackSection,0) as BackSection ");
            sb.AppendFormat("from SaleInfo as s left join dbo.ClientInfo as c on s.ClientId=c.ClientId ");
            sb.AppendFormat("left join dbo.BackMoney as b on b.ClientId=s.ClientId and left(MakeBillDate,7)=b.Month ");
            if (clientId == "0")
            {
                sb.AppendFormat("where left(MakeBillDate,7) >='{0}' and left(MakeBillDate,7)<='{1}' ", sdate, edate);
            }
            else
            {
                sb.AppendFormat("where left(MakeBillDate,7) >='{0}' and left(MakeBillDate,7)<='{1}' and  s.ClientId='{2}' ", sdate, edate, clientId);
            }

            sb.AppendFormat("group by s.ClientId,c.ClientName,left(MakeBillDate,7),b.BackSection order by s.ClientId asc ");
            sqlStr = sb.ToString();
            sqlHelper = new SqlHelper();
            con = sqlHelper.GetSqlConnection();
            cmd = new SqlCommand(sqlStr, con);
            sqlHelper.OpenConnection(con);
            reader = cmd.ExecuteReader();
            IList<SaleInfo> sale_list = new List<SaleInfo>();
            while (reader.Read())
            {
                sale_list.Add(new SaleInfo()
                {
                    ClientId = reader.GetString(0),
                    ClientName = reader.GetString(1),
                    Month = reader.GetString(2),
                    Sale = reader.GetDouble(3),
                    BackSection = reader.GetDouble(4)
                });
            }
            Cache["master"] = sale_list;
            return sale_list;
        }
    }
    /// <summary>
    /// 获取销售明细的列表信息
    /// </summary>
    /// <returns></returns>
    private IList<SaleDetail> Get_Details_List(string clientId, string month)
    {
        if (Cache["details"] != null)
        {
            return (IList<SaleDetail>)Cache["details"];
        }
        else
        {

            sqlStr = string.Format("select BillDate,Style,Number,[Money]/Number as Price,DataOrignal,MakeBillDate,isnull(Remark,'') as Remark from dbo.SaleInfo where ClientId='{0}' and left(MakeBillDate,7)='{1}'", clientId, month);
            con = sqlHelper.GetSqlConnection();
            cmd = new SqlCommand(sqlStr, con);
            sqlHelper.OpenConnection(con);
            reader = cmd.ExecuteReader();
            IList<SaleDetail> detail_List = new List<SaleDetail>();
            while (reader.Read())
            {
                detail_List.Add(new SaleDetail()
                {
                    //RowNum = int.Parse(reader[0].ToString()),
                    BillDate = reader.GetString(0),
                    Style = reader.GetString(1),
                    Number = reader.GetDouble(2),
                    Price = reader.GetDouble(3),
                    DataOrignal = reader.GetString(4),
                    MakeBillDate = reader.GetString(5),
                    Remark = reader.GetString(6)
                });
            }
            sqlHelper.CloseConnection(con);
            Cache["details"] = detail_List;
            return detail_List;
        }
    }

    private void Output_Master_Excel()
    {
        string clientId = Request.QueryString["clientId"],
                sdate = Request.QueryString["sdate"],
                edate = Request.QueryString["edate"];
        IList<SaleInfo> sale_List = Get_Master_List(sdate, edate, clientId);
        var deport_List = sale_List.GroupBy(p => new { p.ClientName, p.ClientId }).Select(p => new { deportName = p.FirstOrDefault().ClientName, deportId = p.FirstOrDefault().ClientId }).ToList();

        HSSFWorkbook hssfWorkBook = new HSSFWorkbook();
        ISheet sheet = hssfWorkBook.CreateSheet("销售汇总报表");

        IFont tempFont;
        ICellStyle firstHeadStyle = hssfWorkBook.CreateCellStyle();//表头的样式
        firstHeadStyle.Alignment = HorizontalAlignment.CENTER_SELECTION;//垂直居中
        firstHeadStyle.VerticalAlignment = VerticalAlignment.CENTER;
        tempFont = hssfWorkBook.CreateFont();
        tempFont.FontHeight = 12 * 18;//设置字体大小
        tempFont.Color = HSSFColor.GREEN.index;//设置颜色
        tempFont.Boldweight = 100 * 20;//加粗
        firstHeadStyle.SetFont(tempFont);

        ICellStyle cellStyle = hssfWorkBook.CreateCellStyle();
        cellStyle.Alignment = HorizontalAlignment.CENTER_SELECTION;
        cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

        DateTime startDate = DateTime.Parse(sdate),
            endDate = DateTime.Parse(edate);
        IRow tmpRow = sheet.CreateRow(1);
        ICell tmpCell = tmpRow.CreateCell(0);
        tmpCell.SetCellValue("客户名称(月初-月末)");
        tmpCell.CellStyle = firstHeadStyle;
        tmpCell = tmpRow.CreateCell(1);
        tmpCell.SetCellValue("客户代码");
        tmpCell.CellStyle = firstHeadStyle;

        int cellCount = 2;
        int cellIndex = 2;
        string tmpDate = string.Empty;
        IList<string> dateList = new List<string>();
        for (DateTime dt = startDate; dt <= endDate; dt = dt.AddMonths(1))
        {
            tmpCell = tmpRow.CreateCell(cellIndex);
            tmpDate = dt.ToString("yyyy-MM");
            dateList.Add(tmpDate);
            tmpCell.SetCellValue(tmpDate);
            tmpCell.CellStyle = firstHeadStyle;
            cellIndex++;
            cellCount++;
        }


        tmpCell = tmpRow.CreateCell(cellIndex);
        tmpCell.SetCellValue("总计");
        tmpCell.CellStyle = firstHeadStyle;

        sheet.SetColumnWidth(0, 20 * 256);

        tmpRow = sheet.CreateRow(0);
        tmpCell = tmpRow.CreateCell(0);
        tmpCell.SetCellValue("销售汇总报表");
        CellRangeAddress tmpRng = new CellRangeAddress(0, 0, 0, cellCount);
        sheet.AddMergedRegion(tmpRng);
        tmpCell.CellStyle = firstHeadStyle;

        IRow tmpBackRow;

        #region 遍历数据
        if (sale_List.Count == 0)
        {
            tmpRow = sheet.CreateRow(2);
            tmpRng = new CellRangeAddress(2, 2, 0, cellCount);
            sheet.AddMergedRegion(tmpRng);
            tmpCell = tmpRow.CreateCell(0);
            tmpCell.SetCellValue("没有查询到任何数据");
            tmpCell.CellStyle = cellStyle;
        }
        else //存在数据
        {
            int rowIndex = 2, colIndex;
            double tmpMoney, tmpBack, monthMoneySum, monthBackSum, deportMoneySum, deportBackSum;
            foreach (var d in deport_List)
            {
                colIndex = 2;
                deportMoneySum = 0; deportBackSum = 0;
                tmpRow = sheet.CreateRow(rowIndex);
                tmpBackRow = sheet.CreateRow(rowIndex + 1);

                tmpCell = tmpRow.CreateCell(0);
                tmpCell.SetCellValue(d.deportName);
                tmpCell.CellStyle = cellStyle;
                tmpCell = tmpRow.CreateCell(1);
                tmpCell.SetCellValue(d.deportId);
                tmpCell.CellStyle = cellStyle;

                tmpCell = tmpBackRow.CreateCell(0);
                tmpCell.SetCellValue("回款");
                tmpCell.CellStyle = cellStyle;

                foreach (string date in dateList)
                {
                    var query = from s in sale_List
                                where s.ClientId == d.deportId && s.Month == date
                                select new
                                {
                                    Money = s.Sale,
                                    BackSection = s.BackSection
                                };
                    if (query.Count() != 0)
                    {
                        tmpCell = tmpRow.CreateCell(colIndex);
                        tmpMoney = query.FirstOrDefault().Money;
                        deportMoneySum += tmpMoney;
                        tmpCell.SetCellValue(tmpMoney);
                        tmpCell.CellStyle = cellStyle;

                        tmpCell = tmpBackRow.CreateCell(colIndex);
                        tmpBack = query.FirstOrDefault().BackSection;
                        deportBackSum += tmpBack;
                        tmpCell.SetCellValue(tmpBack);
                        tmpCell.CellStyle = cellStyle;
                    }
                    else//说明没有符合要求的
                    {
                        tmpCell = tmpRow.CreateCell(colIndex);
                        tmpCell.SetCellValue(0);
                        tmpCell.CellStyle = cellStyle;

                        tmpCell = tmpBackRow.CreateCell(colIndex);
                        tmpCell.SetCellValue(0);
                        tmpCell.CellStyle = cellStyle;

                    }
                    colIndex++;
                }

                tmpCell = tmpRow.CreateCell(colIndex);
                tmpCell.SetCellValue(deportMoneySum);
                tmpCell.CellStyle = cellStyle;
                tmpCell = tmpBackRow.CreateCell(colIndex);
                tmpCell.SetCellValue(deportBackSum);
                tmpCell.CellStyle = cellStyle;

                rowIndex += 2;
            }
            tmpRow = sheet.CreateRow(rowIndex);
            tmpCell = tmpRow.CreateCell(0);
            tmpCell.SetCellValue("汇总");
            tmpCell.CellStyle = cellStyle;
            tmpRng = new CellRangeAddress(rowIndex, rowIndex + 1, 0, 0);
            sheet.AddMergedRegion(tmpRng);

            tmpCell = tmpRow.CreateCell(1);
            tmpCell.SetCellValue("销售汇总");
            tmpCell.CellStyle = cellStyle;

            tmpBackRow = sheet.CreateRow(rowIndex + 1);
            tmpCell = tmpBackRow.CreateCell(1);
            tmpCell.CellStyle = cellStyle;
            tmpCell.SetCellValue("回款汇总");

            string tmpV = string.Empty;
            for (int j = 2; j <= cellIndex; j++)//多少列
            {
                monthMoneySum = 0; monthBackSum = 0;
                int i;
                for (i = 3; i <= rowIndex - 2; i = i + 2)//对每一列进行汇总
                {
                    tmpRow = sheet.GetRow(i);

                    monthMoneySum += tmpRow.GetCell(j).NumericCellValue;


                    tmpBackRow = sheet.GetRow(i + 1);

                    monthBackSum += tmpBackRow.GetCell(j).NumericCellValue;


                }
                tmpRow = sheet.GetRow(i + 1);
                tmpCell = tmpRow.CreateCell(j);
                tmpCell.SetCellValue(monthBackSum);
                tmpCell.CellStyle = cellStyle;

                tmpBackRow = sheet.GetRow(i + 2);
                tmpCell = tmpBackRow.CreateCell(j);
                tmpCell.SetCellValue(monthMoneySum);
                tmpCell.CellStyle = cellStyle;
            }

        }
        #endregion

        #region 下载
        Response.ContentType = "application/vnd.ms-excel";//这里是将文件下载到客户端
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename=Master.xls"));
        Response.Clear();
        MemoryStream ms = new MemoryStream();
        hssfWorkBook.Write(ms);
        Response.BinaryWrite(ms.GetBuffer());
        Response.End();
        #endregion
    }
    private void Output_Details_Excel()
    {
        //HSSFWorkbook workbook = new HSSFWorkbook();
        //DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();//详细信息
        //dsi.Company = "EMPOX";//公司名
        //workbook.DocumentSummaryInformation = dsi;
        //SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        //si.Subject = "EMPOX Report";//主题
        //workbook.SummaryInformation = si;
        string clientId = Request.QueryString["clientId"],
                month = Request.QueryString["month"],
                clientName = HttpUtility.UrlDecode(Request.QueryString["clientName"]);
        IList<SaleDetail> details_List = Get_Details_List(clientId, month);

        HSSFWorkbook hssfWorkBook = null;
        string path = Server.MapPath("~/template/Details.xls");//通过模板获取excel文件
        using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
        {
            hssfWorkBook = new HSSFWorkbook(file);
        }
        //获取模板中的sheet0
        ISheet sheet = hssfWorkBook.GetSheetAt(0);//获取第一个工作表
        IRow tmpRow;
        tmpRow = sheet.GetRow(1);
        tmpRow.GetCell(3).SetCellValue(clientName);
        tmpRow.GetCell(5).SetCellValue(month);

        ICellStyle cellStyle = hssfWorkBook.CreateCellStyle();
        cellStyle.Alignment = HorizontalAlignment.CENTER_SELECTION;//垂直居中
        cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

        ICell tmpCell;
        int index = 3, xh = 1;
        double numSum = 0;
        foreach (SaleDetail sd in details_List)
        {
            tmpRow = sheet.CreateRow(index);
            tmpCell = tmpRow.CreateCell(0);
            tmpCell.SetCellValue(xh);
            tmpCell.CellStyle = cellStyle;
            tmpCell = tmpRow.CreateCell(1);
            tmpCell.SetCellValue(sd.BillDate);
            tmpCell.CellStyle = cellStyle;
            tmpCell = tmpRow.CreateCell(2);
            tmpCell.SetCellValue(sd.Style.Substring(0, 2) + "********");
            tmpCell.CellStyle = cellStyle;

            tmpCell = tmpRow.CreateCell(3);
            numSum += sd.Number;
            tmpCell.SetCellValue(sd.Number);
            tmpCell.CellStyle = cellStyle;

            tmpCell = tmpRow.CreateCell(4);
            tmpCell.SetCellValue(sd.Price);
            tmpCell.CellStyle = cellStyle;

            tmpCell = tmpRow.CreateCell(5);
            tmpCell.SetCellValue(sd.DataOrignal);
            tmpCell.CellStyle = cellStyle;

            tmpCell = tmpRow.CreateCell(6);
            tmpCell.SetCellValue(sd.MakeBillDate);
            tmpCell.CellStyle = cellStyle;

            tmpCell = tmpRow.CreateCell(7);
            tmpCell.SetCellValue(sd.Remark);
            tmpCell.CellStyle = cellStyle;
            index++;
            xh++;
        }

        tmpRow = sheet.CreateRow(index);
        tmpCell = tmpRow.CreateCell(0);
        tmpCell.SetCellValue("汇总");
        tmpCell.CellStyle = cellStyle;

        tmpCell = tmpRow.CreateCell(3);
        tmpCell.SetCellValue(numSum);
        tmpCell.CellStyle = cellStyle;


        #region 下载
        Response.ContentType = "application/vnd.ms-excel";//这里是将文件下载到客户端
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename=Details.xls"));
        Response.Clear();
        MemoryStream ms = new MemoryStream();
        hssfWorkBook.Write(ms);
        Response.BinaryWrite(ms.GetBuffer());
        Response.End();
        #endregion

    }
}

